﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace iBing.Common.DB
{
    public class DbPage
    {
        private string tableName; //要查询的数据表
        private string tableField; //要查询的字段列表（默认为所有）
        private string id;   //表主键
        private string strCondition;//查询条件
        private int pageSize;  //每页显示记录数
        private int pageCurrent; //当前页
        private int pageCount;  //总页数   ---首先计算
        private string pageCode; //分页代码字符串
        private string linkStr;  //连接字符串
        private int counts;   //总记录数   ---首先计算
        private string sqlStr;  //保存动态组合的sql查询语句
        private string orderType; //排序方法 默认升序

        /// <summary>
        /// 初始化类
        /// </summary>
        public DbPage()
        {
            pageSize = 20;
            pageCurrent = 1;
            pageCount = 1;
            counts = 1;
            tableField = "*";
            linkStr = "?page=";
            sqlStr = "";
            strCondition = "1=1";
            //orderList  = " ID ";
            orderType = "ASC";
            counts = 0;
        }
        #region   分页类属性设置
        /// <summary>
        /// 定义连接字符串属性
        /// </summary>
        public string ConnStr
        {
            get
            {
                return PubConstant.ConnectionString;
            }
        }
        /// <summary>
        /// 分页代码字符串
        /// </summary>
        public string PageCode
        {
            get
            {
                return this.pageCode.Trim();
            }
            set
            {
                this.pageCode = value;
            }
        }
        /// <summary>
        /// 分页代码中的连接字符
        /// </summary>
        public string LinkStr
        {
            get
            {
                return this.linkStr;
            }
            set
            {
                this.linkStr = value;
            }
        }
        /// <summary>
        /// 数据表名
        /// </summary>
        public string TableName
        {
            get
            {
                return this.tableName;
            }
            set
            {
                this.tableName = value;
            }
        }
        /// <summary>
        /// 查询的字段列表
        /// </summary>
        public string TableField
        {
            get
            {
                return this.tableField;
            }
            set
            {
                this.tableField = value;
            }
        }
        /// <summary>
        /// 每页显示记录数
        /// </summary>
        public int PageSize
        {
            get
            {
                return this.pageSize;
            }
            set
            {
                this.pageSize = value;
            }
        }
        /// <summary>
        /// 当前显示页码
        /// </summary>
        public int PageCurrent
        {
            get
            {
                return this.pageCurrent;
            }
            set
            {
                this.pageCurrent = value;
            }
        }
        /// <summary>
        /// 查询条件
        /// </summary>
        public string StrCondition
        {
            get
            {
                return this.strCondition;
            }
            set
            {
                this.strCondition = value;
            }
        }
        /// <summary>
        /// 主表的主键
        /// </summary>
        public string ID
        {
            get
            {
                return this.id;
            }
            set
            {
                this.id = value;
            }
        }
        /// <summary>
        ///  排序方法
        /// </summary>
        public string OrderType
        {
            get
            {
                return this.orderType;
            }
            set
            {
                this.orderType = value;
            }
        }


        #endregion
        #region 初始化总记录数和总页数设置
        /// <summary>
        /// 查询总记录数
        /// </summary>
        private void GetCounts()
        {
            string str = "Select Count(1) From " + tableName + " Where " + strCondition;
            DataTable myDT = this.GetDataTable(str);
            if (myDT.Rows.Count != 0)
            {
                this.counts = Convert.ToInt32(myDT.Rows[0][0].ToString());
            }
            else
            {
                this.counts = 0;
            }
        }
        private void SetPageCount()
        {
            if (this.counts < this.pageSize)
            {
                this.pageCount = 1;
            }
            else
            {
                this.pageCount = (this.counts / this.pageSize) + 1;
            }
        }
        #endregion

        #region   分页类相关方法
        /// <summary>
        /// 建立数据库连接对象
        /// </summary>
        /// <returns></returns>
        private OleDbConnection GreateConnection()
        {
            try
            {
                return new OleDbConnection(this.ConnStr);
            }
            catch
            {
                throw new ArgumentNullException("Access 数据库文件不存在！");
            }
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        private DataTable GetDataTable(string sqlStr)
        {
            using (OleDbConnection conn = this.GreateConnection())
            {
                OleDbCommand cmd = new OleDbCommand(sqlStr, conn);
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                da.Dispose();
                cmd.Dispose();
                conn.Close();
                return ds.Tables[0];
            }
        }

        #endregion

        #region 返回查询数据结果

        /// <summary>
        /// 动态组合查询语句
        /// </summary>
        private void SetSqlStr()
        {
            if (this.pageCurrent == 1)
            {
                this.sqlStr = "Select Top " + this.pageSize + " " + this.tableField + " From " + this.tableName + " Where " + this.strCondition + " Order By " + this.id + " " + this.orderType;
            }
            else if (this.pageCurrent >= this.pageCount)
            {
                this.sqlStr += "SELECT * FROM ( SELECT TOP " + (this.counts - pageSize * (pageCurrent - 1)) + " * FROM (";
                this.sqlStr += "SELECT TOP " + this.pageSize * (this.pageCurrent) + " * FROM " + this.tableName;
                this.sqlStr += " Where " + this.strCondition;
                this.sqlStr += " ORDER BY " + this.id + " " + this.orderType;
                this.sqlStr += ") TableA";
                if (this.orderType.ToUpper() == "ASC")
                {
                    this.sqlStr += " ORDER BY " + this.id + " DESC";
                }
                else
                {
                    this.sqlStr += " ORDER BY " + this.id + " ASC";
                }
                this.sqlStr += ") TableB";
                this.sqlStr += " ORDER BY " + this.id + " " + this.orderType;
            }
            else
            {
                this.sqlStr += "SELECT * FROM ( SELECT TOP " + this.pageSize + " * FROM (";
                this.sqlStr += "SELECT TOP " + this.pageSize * (this.pageCurrent) + " * FROM " + this.tableName;
                this.sqlStr += " Where " + this.strCondition;
                this.sqlStr += " ORDER BY " + this.id + " " + this.orderType;
                this.sqlStr += ") TableA";
                if (this.orderType.ToUpper() == "ASC")
                {
                    this.sqlStr += " ORDER BY " + this.id + " DESC";
                }
                else
                {
                    this.sqlStr += " ORDER BY " + this.id + " ASC";
                }
                this.sqlStr += ") TableB";
                this.sqlStr += " ORDER BY " + this.id + " " + this.orderType;
            }
        }


        /// <summary>
        /// 数据加载
        /// </summary>
        public DataTable LoadInfo()
        {
            this.GetCounts();
            this.SetPageCount();
            this.SetSqlStr();
            this.page_code();
            return this.GetDataTable(this.sqlStr);
        }
        #endregion

        #region 分页字符串
        private void page_code()
        {
            if (this.linkStr == null)
            {
                this.linkStr = "?page=";
            }
            else if (this.linkStr == "")
            {
                this.linkStr = "?page=";
            }
            else if (this.linkStr != "?page=")
            {
                this.linkStr += "&page=";
            }
            string code_string = "";
            //int count1=this.counts;
            if (pageCurrent <= 0)
            {
                pageCurrent = 1;
            }
            if (pageCurrent > this.pageCount)
            {
                pageCurrent = this.pageCount;
            }
            code_string = "当前第<font color=red>" + pageCurrent.ToString() + "</font>页共<font color=red>" + this.pageCount.ToString() + "</font>页  ";
            if (pageCurrent <= 1)
            {
                code_string += " <font style=\"{color:#848484;filter:DropShadow(Color=#FFFFFF, OffX=1, OffY=1, Positive=2);height:12px}\">首页</font> ";
                code_string += " <font style=\"{color:#848484;filter:DropShadow(Color=#FFFFFF, OffX=1, OffY=1, Positive=2);height:12px}\">上一页</font> ";
            }
            else
            {
                code_string += "<a href=" + this.linkStr + "1>首页</a> ";
                code_string += " <a href=" + this.linkStr + (pageCurrent - 1).ToString() + ">上一页</a> ";
            }
            if (this.pageCurrent >= this.pageCount)
            {
                code_string += " <font style=\"{color:#848484;filter:DropShadow(Color=#FFFFFF, OffX=1, OffY=1, Positive=2);height:12px}\">下一页</font> ";
                code_string += " <font style=\"{color:#848484;filter:DropShadow(Color=#FFFFFF, OffX=1, OffY=1, Positive=2);height:12px}\">尾页</font> ";
            }
            else
            {
                code_string += " <a href=" + this.linkStr + (pageCurrent + 1).ToString() + ">下一页</a> ";
                code_string += "<a href=" + this.linkStr + this.pageCount.ToString() + "> 尾页 </a> ";
            }
            code_string += "跳转到 <select onchange=\"if(this.options[this.selectedIndex].value!=''){document.location.href='" + this.linkStr + "'+this.options[this.selectedIndex].value;}\">";
            for (int i = 0; i < this.pageCount; i++)
            {
                if (this.pageCurrent == (i + 1))
                {
                    code_string += "<option value=" + (i + 1) + " selected>" + (i + 1) + "</option>";
                }
                else
                {
                    code_string += "<option value=" + (i + 1) + ">" + (i + 1) + "</option>";
                }
            }
            code_string += "</select> 页";
            this.pageCode = code_string;
        }
        #endregion
    }
}
